from common import db_utils as util
from common import df_operator as op

def __toTR0103(start_time,end_time):
    del_sql = """
         DELETE FROM BGTARAS1.T_ADS_FACT_SICB_TR0103
    WHERE PRODUCE_TIME >=  '""" + start_time + """' AND PRODUCE_TIME<= '""" + end_time + """'
    """
    conn_rds.execute(del_sql)

    sql = """
        SELECT 
                C.COST_CENTER,
                C.UNIT_CODE,
                C.CLASS,
                C.SHIFT,
                C.PRODUCE_TIME,
                C.PROD_COILNO,
                C.ENTRY_COILNO,
                C.THICK,
                C.THICK_CODE,
                C.WIDTH,
                C.WIDTH_CODE,
                C.ANNEAL_CURVE,
                C.TRIM_FLAG,
                C.ENTRY_WIDTH,
                C.ENTRY_WIDTH_CODE,
                C.ENTRY_THICK,
                C.ENTRY_THICK_CODE,
                C.PLAN_NO_C,
                C.PLAN_NO_CODE,
                C.TRIMMING_WIDTH_RANGE,
                C.TRIMMING_WIDTH_CODE,
                C.IN_MAT_DIV_CODE,
                C.IN_MAT_DIV,
                C.PRODUCE_NICK_FLAG,
                C.PROD_CAT_CODE,
                C.PROD_CAT,
                SUM(CASE WHEN B.TYPE='1' THEN 1 ELSE 0 END) AS YIELD_DRIVER_TOTAL,
                SUM(CASE WHEN B.TYPE='1' AND (A.ACT_VALUE > A.TARGET_RANGE_UL OR A.ACT_VALUE < A.TARGET_RANGE_LL) THEN 1 ELSE 0 END) AS YIELD_DRIVER_ABNOR_NUM,
                SUM(CASE WHEN B.TYPE='2' THEN 1 ELSE 0 END) AS ENERGY_DRIVER_TOTAL,
                SUM(CASE WHEN B.TYPE='2' AND (A.ACT_VALUE > A.TARGET_RANGE_UL OR A.ACT_VALUE < A.TARGET_RANGE_LL) THEN 1 ELSE 0 END) AS ENERGY_DRIVER_ABNOR_NUM,
                SUM(CASE WHEN B.TYPE='3' THEN 1 ELSE 0 END) AS ACCES_DRIVER_TOTAL,
                SUM(CASE WHEN B.TYPE='3' AND (A.ACT_VALUE > A.TARGET_RANGE_UL OR A.ACT_VALUE < A.TARGET_RANGE_LL) THEN 1 ELSE 0 END) AS ACCES_DRIVER_ABNOR_NUM,
                'BGRAGGCB' AS REC_CREATOR,
                TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME
     FROM BGTARAS1.T_ADS_FACT_SICB_CD0201 C   
     LEFT JOIN BGTARAS1.T_ADS_WH_SICB_MO0101 B ON C.UNIT_CODE=B.UNIT_CODE
     LEFT JOIN BGTARAS1.T_ADS_FACT_SICB_MO0101 A 
     ON A.PROD_COILNO=C.PROD_COILNO AND A.UNIT_CODE=C.UNIT_CODE
     AND B.IND=A.IND
     WHERE C.PRODUCE_TIME  >=  '""" + start_time + """' AND C.PRODUCE_TIME<= '""" + end_time + """'
          GROUP BY C.COST_CENTER,
                C.UNIT_CODE,
                C.CLASS,
                C.SHIFT,
                C.PRODUCE_TIME,
                C.PROD_COILNO,
                C.ENTRY_COILNO,
                C.THICK,
                C.THICK_CODE,
                C.WIDTH,
                C.WIDTH_CODE,
                C.ANNEAL_CURVE,
                C.TRIM_FLAG,
                C.ENTRY_WIDTH,
                C.ENTRY_WIDTH_CODE,
                C.ENTRY_THICK,
                C.ENTRY_THICK_CODE,
                C.PLAN_NO_C,
                C.PLAN_NO_CODE,
                C.TRIMMING_WIDTH_RANGE,
                C.TRIMMING_WIDTH_CODE,
                C.IN_MAT_DIV_CODE,
                C.IN_MAT_DIV,
                C.PRODUCE_NICK_FLAG,
                C.PROD_CAT_CODE,
                C.PROD_CAT
    """
    print(sql)
    df_val = util.query(conn_rds, sql)
    dtypedict = op.get_dict_type(df_val)
    util.insert(df_val, conn_rds, "T_ADS_FACT_SICB_TR0103", "BGTARAS1", dtypedict)


def TR0103(start_time,end_time):
    global conn_rds
    conn_rds = util.getConnectionDb2('10.70.48.121', '50000 ', 'RG0PROD', 'bgtaroq000', 'bgtaroq000bgta')
    # conn_mpp = util.getConnectionDb2('10.70.49.21', '50021 ', 'BGBDPROD', 'bgmoq000', 'bgmoq000bgta')
    __toTR0103(start_time,end_time)
    conn_rds.close()
    # conn_mpp.close()

if __name__ == '__main__':
    TR0103()